﻿using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using myproject.method;
using System.Security.Cryptography;
using System.Data;

namespace supermarket.method
{
    internal class Classuser
    {
        SqlConnection con = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        SqlDataReader qlddr = null;  //数据缓冲区
        #region ///admin登录
        public bool adminselect(string username1, string userpasswd1)//用户身份验证
        {
            string md5paw= CommonHelper.GetMD5Str(userpasswd1);
            try
            {
                //1.连接数据库
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();//数据库已经连接并打开
                                      //2.执行sql语句（select
                string s;
                s = "select * from Table_admin where username='" + username1 + "' and passwd='" + md5paw + "'";     //SQL语句（select）

                cmd = new SqlCommand(s, con);//执行sql语句
                qlddr = cmd.ExecuteReader(); //把sql语句执行结果读取到数据缓冲区qllddr中
                qlddr.Read();//读取数据缓冲中的内容
                if (qlddr.HasRows)
                {
                    qlddr.Close();
                    return true; //3.反馈给调用者（函数的调用语句）
                }
                else
                {
                    qlddr.Close();
                    return false; //3.反馈给调用者（函数的调用语句）
                };
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return false;
            }
        }
        #endregion

        #region 登录
        public (bool success, string role, string tip) AdminSelect ( string username1, string userpasswd1 ) // 用户身份验证
        {
            try
            {
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();

                // 执行 SQL 语句（SELECT）
                string s = "SELECT * FROM SystemAccounts WHERE username = @username AND passwd = @passwd";

                cmd = new SqlCommand(s, con);
                cmd.Parameters.AddWithValue("@username", username1);
                cmd.Parameters.AddWithValue("@passwd", userpasswd1);

                qlddr = cmd.ExecuteReader();
                if (qlddr.Read() && qlddr.HasRows)
                {
                    string role = qlddr["role"].ToString();
                    string tip = qlddr["tip"].ToString();
                    qlddr.Close();
                    return (true, role, tip);
                }
                else
                {
                    qlddr.Close();
                    return (false, string.Empty, string.Empty); // 验证失败时返回空值
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return (false, string.Empty, string.Empty); // 异常时返回空值
            }
        }
        #endregion

        #region ///会员身份验证
        public bool userselect(string usernumber, string username)//用户身份验证
        {

            try
            {
                //1.连接数据库
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();//数据库已经连接并打开
                                      //2.执行sql语句（select
                string s;

                if (usernumber == "" && username == "")
                {
                    s = "select * from Table_user";     //SQL语句（select）
                }
                else if (usernumber != "" && username == "")
                {
                    s = "select * from Table_user where number='" + usernumber + "'";     //SQL语句（select）
                }
                else
                {
                    s = "select * from Table_user where name='" + username + "' and number='" + usernumber + "'";     //SQL语句（select）
                }



                cmd = new SqlCommand(s, con);//执行sql语句
                qlddr = cmd.ExecuteReader(); //把sql语句执行结果读取到数据缓冲区qllddr中
                qlddr.Read();//读取数据缓冲中的内容
                if (qlddr.HasRows)
                {
                    qlddr.Close();
                    return true; //3.反馈给调用者（函数的调用语句）
                }
                else
                {
                    qlddr.Close();
                    return false; //3.反馈给调用者（函数的调用语句）
                };
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return false;
            }
        }

        #endregion

        #region ///新建会员
        public int usernew(string username,string number)
        {
            int num = 0;
            try
            {
                //1.连接数据库

                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                //2.执行SQL语句（select）
                string s;
                s = "insert into Table_user values('" + username + "','" + number + "','普通会员',0)";    //SQL语句（select）
                cmd = new SqlCommand(s, con);   //执行SQL语句
                num = cmd.ExecuteNonQuery();//把sql语句执行结果读取到数据缓冲区qllddr中
                return num;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return num = 2;
            }

        }

        #endregion

        #region //获取表中最后一个用户编码
        public string Getuserid()
        {
            string s;
            string baseid = "";
            //1.连接数据库
            Classgetsql sqlcon = new Classgetsql();
            con = sqlcon.getcon();  //数据库已连接并打开
                                    //2.执行SQL语句（select）
            s = "select * from Table_user order by id";     //SQL语句（select）
            cmd = new SqlCommand(s, con);   //执行SQL语句
            qlddr = cmd.ExecuteReader();   //把sql语句执行结果读取到数据缓冲区qlddr中
            while (qlddr.Read())
            {
                baseid = qlddr[0].ToString();
            }
            qlddr.Close();
            return baseid;
        }
        #endregion

        #region///删除会员
        public int deleteuser(string number)
        {
            int i = 0;
            try
            {
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                s = "delete from Table_user where number='" + number + "'";
                cmd = new SqlCommand(s, con);
                i = cmd.ExecuteNonQuery();      //受影响的行数
                return i;
            }
            catch (Exception ex)
            {
                i = 2;
                return i;
            }
        }

        #endregion

        #region///读取用户信息到数据表中,删除
        public void deleteuserinfotodv(DataGridView dv)
        {
            string s;
            int i = 0;
            //1.连接数据库
            Classgetsql sqlcon = new Classgetsql();
            con = sqlcon.getcon();  //数据库已连接并打开
                s = "select * from Table_user";   //无条件查询

            cmd = new SqlCommand(s, con);   //执行SQL语句
            qlddr = cmd.ExecuteReader();   //把sql语句执行结果读取到数据缓冲区qlddr中
            while (qlddr.Read())
            {
                dv[0, i].Value = qlddr[0].ToString();
                dv[1, i].Value = qlddr[1].ToString();
                dv[2, i].Value = qlddr[2].ToString();
                dv[3, i].Value = qlddr[3].ToString();
                dv[4, i].Value = qlddr[4].ToString();
                i++;
            }
            qlddr.Close();
        }

        #endregion

        #region///读取用户信息到数据表中,查询
        public void selectuserinfotodv(DataGridView dv, string uname, string unumber)
        {
            string s;
            int i = 0;
            //1.连接数据库
            Classgetsql sqlcon = new Classgetsql();
            con = sqlcon.getcon();  //数据库已连接并打开
            if (uname == "" && unumber == "")
            {
                s = "select * from Table_user";   //无条件查询
            }
            else if (uname != "" && unumber == "")
            {
                s = "select * from Table_user where name like '%" + uname + "%'";   //根据用户名查询
            }
            else
            {
                s = "select * from Table_user where number like '%" + unumber + "%'";    //根据用户id查询
            }

            cmd = new SqlCommand(s, con);   //执行SQL语句
            qlddr = cmd.ExecuteReader();   //把sql语句执行结果读取到数据缓冲区qlddr中
            while (qlddr.Read())
            {
                dv[0, i].Value = qlddr[0].ToString();
                dv[1, i].Value = qlddr[1].ToString();
                dv[2, i].Value = qlddr[2].ToString();
                dv[3, i].Value = qlddr[3].ToString();
                dv[4, i].Value = qlddr[4].ToString();
                i++;
            }
            qlddr.Close();
        }

        #endregion

        #region  //根据当前登录的用户名查询其手机号
        public string selectnumber(string us)
        {
            try
            {
                //1.连接数据库
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                //2.执行SQL语句（select）
                string s;
                string number = "";
                s = "select * from Table_user where name='" + us + "'";
                cmd = new SqlCommand(s, con);   //执行SQL语句
                qlddr = cmd.ExecuteReader();   //把sql语句执行结果读取到数据缓冲区qlddr中
                while (qlddr.Read())       //读取数据缓存区中的内容
                {
                    number = qlddr[1].ToString();        //3.反馈给调用者（函数的调用语句）
                }
                qlddr.Close();
                return number;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return ex.ToString();
            }
        }
        #endregion

        #region   ///用户手机号的修改
        public int upusernumber(string uname, string unewnumber)
        {
            int i = 0;
            try
            {
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                s = "update Table_user set number ='" + unewnumber + "' where name='" + uname + "'";
                cmd = new SqlCommand(s, con);
                i = cmd.ExecuteNonQuery();      //受影响的行数
                return i;
            }
            catch (Exception ex)
            {
                i = 2;
                return i;
            }

        }
        #endregion

        #region   ///管理员修改手机号
        public int upsupnumber(string uname, string unewnumber)
        {
            int i = 0;
            try
            {
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                s = "update Table_user set number ='" + unewnumber + "' where name='" + uname + "'";
                cmd = new SqlCommand(s, con);
                i = cmd.ExecuteNonQuery();      //受影响的行数
                return i;
            }
            catch (Exception ex)
            {
                i = 2;
                return i;
            }

        }
        #endregion

        #region ///管理员修改手机号
        public void userandcom(ComboBox comusername)
        {
            try
            {
                //1.连接数据库
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                string s;
                s = "select * from Table_user";     //SQL语句（select）
                cmd = new SqlCommand(s, con);   //执行SQL语句
                qlddr = cmd.ExecuteReader();   //把sql语句执行结果读取到数据缓冲区qlddr中
                while (qlddr.Read())
                {
                    comusername.Items.Add(qlddr[1].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }


        }
        #endregion

        #region///查询导出的内容
        public DataTable getuserall()
        {
            DataTable dt = new DataTable();
            try
            {
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                s = @"SELECT [id] as '序号'
      ,[name] as '姓名'
      ,[number] as '手机号'
      ,[class] as '等级'
      ,[integral] as '积分'
  FROM [Table_user]";
                cmd = new SqlCommand(s, con);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                sqlDataAdapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return dt;
            }
        }

        public DataTable getuserall2()
        {
            DataTable dt = new DataTable();
            try
            {
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                s = @"SELECT class, COUNT(*) AS class_count FROM [Table_user] GROUP BY class";
                cmd = new SqlCommand(s, con);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                sqlDataAdapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return dt;
            }
        }
        #endregion

    }
}
